package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import auxilary.Log;

import entities.Course;
import entities.Session;
import entities.User;
import exceptions.DBException;
import exceptions.MyException;
import exceptions.UserException;

/* ******************************************************** */
/* 						SessionHandler						*/
/*															*/
/*	Takes care of communicating with the database on		*/
/* 	behalf of course sessions								*/
/*															*/
/* ******************************************************** */

public class SessionHandler extends DBHandler {

	/*
	 * Session Available - check if session is available
	 */
	public boolean sessionAvailable(Course course, Session session,
			Connection conn) throws SQLException {
		boolean gotConnetion = (conn != null);
		String query = "Select * From " + Tables.Session.fullDBName + ", "
				+ Tables.Course.fullDBName + " WHERE "
				+ Tables.Session.fullDBName + ".`"
				+ Tables.Session.Columns.COURSE_ID + "`="
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` AND `" + Tables.Course.Columns.GROUP_ID + "`="
				+ String.valueOf(course.getGroupId()) + " AND NOT(`"
				+ Tables.Session.Columns.START_SLOT + "`>="
				+ String.valueOf(session.getEndSlot()) + " OR `"
				+ Tables.Session.Columns.END_SLOT + "`<="
				+ String.valueOf(session.getStartSlot()) + ")";
		// execute query
		ResultSet rs = null;
		if (!gotConnetion) {
			try {
				conn = getConnection();
			} catch (DBException e1) {
				Log.err("CourseHandler[checkSession]: trouble getting query");
			}
		}
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(query);
			return (!rs.first());
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("CourseHandler[checkSession]: trouble getting query");
		} finally {
			if (!gotConnetion) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("can't close result set");
				}
			}
		}
		return false;
	}

	/*
	 * Check if session is
	 */
	public boolean sessionAvailableToUser(Session session, User user,
			Connection conn) throws SQLException {
		boolean gotConnection = (conn == null);
		String query = "SELECT * FROM " + Tables.Session.fullDBName + ", "
				+ Tables.Course.fullDBName + ","
				+ Tables.CourseToUser.fullDBName + " WHERE "
				+ Tables.Session.fullDBName + ".`"
				+ Tables.Session.Columns.COURSE_ID + "`="
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` AND " + Tables.CourseToUser.fullDBName + ".`"
				+ Tables.CourseToUser.Columns.COURSE_ID + "`="
				+ Tables.Course.fullDBName + ".`" + Tables.Course.Columns.ID
				+ "` AND " + Tables.CourseToUser.fullDBName + ".`"
				+ Tables.CourseToUser.Columns.USER_ID + "`='"
				+ String.valueOf(user.getId()) + "' AND "
				+ Tables.CourseToUser.fullDBName + ".`"
				+ Tables.CourseToUser.Columns.IS_REGISTERED + "`=1 AND NOT(`"
				+ Tables.Session.Columns.START_SLOT + "`>="
				+ String.valueOf(session.getEndSlot()) + " OR `"
				+ Tables.Session.Columns.END_SLOT + "`<="
				+ String.valueOf(session.getStartSlot()) + ")";

		// execute query
		ResultSet rs = null;
		if (!gotConnection) {
			try {
				conn = getConnection();
			} catch (DBException e1) {
				Log.err("CourseHandler[checkSession]: trouble getting connection");
			}
		}
		try {
			rs = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(query);
			return (!rs.first());
		} catch (SQLException e) {
			throw e;
		} catch (Exception e) {
			Log.err("CourseHandler[checkSession]: trouble getting query");
		} finally {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close result set");
				}
			}
		}
		return false;
	}

	/*
	 * Add Session - add session to db
	 */
	public int addSession(Session session, Course course, Connection conn)
			throws MyException, SQLException {
		// check session fields
		if (session == null || session.getStartSlot() >= 70
				|| session.getStartSlot() < 0 || session.getEndSlot() > 70
				|| session.getEndSlot() < 0)
			throw new MyException(UserException.BAD_PARAMS);
		// check if session is available
		if (!this.sessionAvailable(course, session, conn))
			throw new DBException(Tables.Session.dbName,
					"fetch course with name: " + course.getName() + " "
							+ session.getStartSlot() + " - "
							+ session.getEndSlot(), "Session already exists",
					DBException.VALUE_ALREADY_EXISTS);
		// insert session to db
		Object[][] data = this.createAddSessionData(session, course);
		int id = super.insert(data,
				Tables.SCHEMA + "." + Tables.Session.dbName, conn);
		return id;
	}

	/*
	 * Fetch Session - get the session with the id from the DB
	 */
	public Session fetchSession(int id) throws MyException {
		// check id
		if (id < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the Session from the DB.
		String where = Tables.Session.Columns.ID + "=" + id;
		return this.doFetchSession(where);
	}

	/*
	 * Fetch All Courses - get all sessions of course from db
	 */
	public List<Session> fetchAllCourseSessions(Course course, Connection conn)
			throws MyException, SQLException {
		boolean gotConnection = (conn != null);
		// get all course sessions from the DB.
		ResultSet rs = null;
		List<Session> sessions = null;
		try {
			String where = Tables.Session.Columns.COURSE_ID + "="
					+ course.getId();
			rs = getSessionResultSet(where, conn);
			sessions = buildSessionsList(rs, conn);
		} catch(SQLException e) {
			throw e;
		} finally {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close result set");
				}
			}
		}
		return sessions;
	}

	/*
	 * Delete all sessions of Course - delete all sessions of Course with id
	 */
	public void deleteSessionsOfCourse(int id) throws MyException {
		// check id
		if (id < 0)
			throw new MyException(UserException.BAD_PARAMS);

		// get the Session from the DB.
		String where = Tables.Session.Columns.COURSE_ID + "=" + id;
		ResultSet rs = null;
		try {
			rs = getSessionResultSet(where, null);
			if (rs.first()) {
				rs.deleteRow();
				while (rs.next()) {
					rs.deleteRow();
				}
			}
		} catch (Exception e) {
			throw new MyException();
		} finally {
			closeConnection(rs);
		}
	}

	/* ******* */
	/* Helpers */
	/* ******* */

	/*
	 * Do Fetch Session - do the fetching of the session
	 */
	private Session doFetchSession(String where) throws DBException {
		ResultSet rs = null;
		Session session = null;
		try {
			rs = getSessionResultSet(where, null);
			session = resultSetToSession(rs, false, true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection(rs);
		}
		return session;
	}

	/*
	 * Get Session ResultSet - fetch session ResultSet from the db
	 */
	private ResultSet getSessionResultSet(String where, Connection conn)
			throws DBException, SQLException {
		boolean gotConnection = (conn != null);
		ResultSet rs = super.fetch(Tables.SCHEMA + "." + Tables.Session.dbName,
				where, conn);
		if (this.isEmptyResultSet(rs, Tables.SCHEMA + "."
				+ Tables.Session.dbName)) {
			if (!gotConnection) {
				closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close resultset");
				}
			}
			throw new DBException(Tables.Session.dbName, null,
					"Value not found", DBException.VALUE_NOT_FOUND);
		}
		return rs;
	}

	/*
	 * ResultSet to Session - converts ResultSet to Session
	 */
	private Session resultSetToSession(ResultSet rs, boolean isClose,
			boolean isNeedNext) throws DBException {
		Session session = null;
		try {
			if (isNeedNext)
				rs.next();
			session = new Session(rs.getInt(Tables.Session.Columns.ID),
					rs.getInt(Tables.Session.Columns.START_SLOT),
					rs.getInt(Tables.Session.Columns.END_SLOT));

		} catch (SQLException e) {
			throw new DBException(Tables.Session.dbName, null, e.getMessage(),
					DBException.CLOSE);
		} finally {
			if (isClose)
				this.closeConnection(rs);
		}
		return session;
	}

	/*
	 * Create Add Session Data - create session data for add to db
	 */
	private Object[][] createAddSessionData(Session session, Course course) {
		Object[][] data = new Object[3][2];
		// create data array
		data[0][COL_NAME] = Tables.Session.Columns.START_SLOT;
		data[0][COL_VAL] = session.getStartSlot();
		data[1][COL_NAME] = Tables.Session.Columns.END_SLOT;
		data[1][COL_VAL] = session.getEndSlot();
		data[2][COL_NAME] = Tables.Session.Columns.COURSE_ID;
		data[2][COL_VAL] = course.getId();
		return data;
	}

	/*
	 * Build Session List - build session list from rs
	 */
	private List<Session> buildSessionsList(ResultSet rs, Connection conn)
			throws DBException, SQLException {
		ArrayList<Session> sessions = new ArrayList<Session>();

		// extract all items from ResultSet
		try {
			rs.first();
			while (!rs.isAfterLast()) {
				sessions.add(this.resultSetToSession(rs, false, false));
				rs.next();
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			if (conn == null) {
				this.closeConnection(rs);
			} else {
				try {
					rs.close();
				} catch (Exception e) {
					Log.err("couldn't close resultset");
				}
			}
		}
		return sessions;
	}
}
